﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
namespace BanHangCshap.Class
{
    class KhachHangNCC:Database
    {
       #region ***** Fields & Properties ***** 
		private string _makhncc;
		public string makhncc
		{ 
			get 
			{ 
				return _makhncc;
			}
			set 
			{ 
				_makhncc = value;
			}
		}
		private string _tenkhncc;
		public string tenkhncc
		{ 
			get 
			{ 
				return _tenkhncc;
			}
			set 
			{ 
				_tenkhncc = value;
			}
		}
		private string _diachikhncc;
		public string diachikhncc
		{ 
			get 
			{ 
				return _diachikhncc;
			}
			set 
			{ 
				_diachikhncc = value;
			}
		}
		private string _sodtkhncc;
		public string sodtkhncc
		{ 
			get 
			{ 
				return _sodtkhncc;
			}
			set 
			{ 
				_sodtkhncc = value;
			}
		}
		private string _emailkhncc;
		public string emailkhncc
		{ 
			get 
			{ 
				return _emailkhncc;
			}
			set 
			{ 
				_emailkhncc = value;
			}
		}
		private string _faxkhncc;
		public string faxkhncc
		{ 
			get 
			{ 
				return _faxkhncc;
			}
			set 
			{ 
				_faxkhncc = value;
			}
		}
		private string _sotaikhoankhncc;
		public string sotaikhoankhncc
		{ 
			get 
			{ 
				return _sotaikhoankhncc;
			}
			set 
			{ 
				_sotaikhoankhncc = value;
			}
		}
		private string _masothuekhncc;
		public string masothuekhncc
		{ 
			get 
			{ 
				return _masothuekhncc;
			}
			set 
			{ 
				_masothuekhncc = value;
			}
		}
		private string _ghichukhncc;
		public string ghichukhncc
		{ 
			get 
			{ 
				return _ghichukhncc;
			}
			set 
			{ 
				_ghichukhncc = value;
			}
		}
		
        private double _congnohientaikhncc;
        public double congnobandaukhncc
        {
            get
            {
                return _congnohientaikhncc;
            }
            set
            {
                _congnohientaikhncc = value;
            }
        }
		private int _loaikhncc;
		public int loaikhncc
		{ 
			get 
			{ 
				return _loaikhncc;
			}
			set 
			{ 
				_loaikhncc = value;
			}
		}
		private string _manhomkhncc;
		public string manhomkhncc
		{ 
			get 
			{ 
				return _manhomkhncc;
			}
			set 
			{ 
				_manhomkhncc = value;
			}
		}
		#endregion

		#region ***** Init Methods ***** 
		public KhachHangNCC()
		{
		}
        public KhachHangNCC(string makhncc)
        {
            string sql = "select * from KhachHangNCC where makhncc = '" + makhncc + "'";
            foreach (DataRow dr in getDataTable(sql).Rows)
            {
                this.makhncc = makhncc;
                this.tenkhncc = dr["tenkhncc"].ToString().Trim();
                this.diachikhncc = dr["diachikhncc"].ToString().Trim();
                this.sodtkhncc = dr["sodtkhncc"].ToString().Trim();
                this.emailkhncc = dr["emailkhncc"].ToString().Trim();
                this.faxkhncc = dr["faxkhncc"].ToString().Trim();
                this.sotaikhoankhncc = dr["sotaikhoankhncc"].ToString().Trim();
                this.masothuekhncc = dr["masothuekhncc"].ToString().Trim();
                this.ghichukhncc = dr["ghichukhncc"].ToString().Trim();
                this.congnobandaukhncc = double.Parse(dr["congnobandaukhncc"].ToString().Trim());
                this.loaikhncc = int.Parse(dr["loaikhncc"].ToString());
                this.manhomkhncc = dr["manhomkhncc"].ToString().Trim();
            }
        }
        //public KhachHangNCC(int makhncc, string tenkhncc, string diachikhncc, string sodtkhncc, string emailkhncc, string faxkhncc, string sotaikhoankhncc, string masothuekhncc, string ghichukhncc, decimal nodaukikhncc, int loaikhncc, int manhomkhncc)
        //{
        //    this.makhncc = makhncc;
        //    this.tenkhncc = tenkhncc;
        //    this.diachikhncc = diachikhncc;
        //    this.sodtkhncc = sodtkhncc;
        //    this.emailkhncc = emailkhncc;
        //    this.faxkhncc = faxkhncc;
        //    this.sotaikhoankhncc = sotaikhoankhncc;
        //    this.masothuekhncc = masothuekhncc;
        //    this.ghichukhncc = ghichukhncc;
        //    this.nodaukikhncc = nodaukikhncc;
        //    this.loaikhncc = loaikhncc;
        //    this.manhomkhncc = manhomkhncc;
        //}
		#endregion
        public Boolean themKhachHangNCC()
        {
            string sql = "insert into KhachHangNCC(makhncc,tenkhncc,diachikhncc,sodtkhncc,emailkhncc,faxkhncc,sotaikhoankhncc,masothuekhncc,ghichukhncc,congnobandaukhncc,loaikhncc,manhomkhncc) ";
            sql += "values ('" + makhncc + "',N'" + tenkhncc + "',N'" + diachikhncc + "','" + sodtkhncc + "','" + emailkhncc + "','" + faxkhncc + "','" + sotaikhoankhncc + "','" + masothuekhncc + "',N'" + ghichukhncc + "'," + congnobandaukhncc + "," + loaikhncc + ",'" + manhomkhncc + "')";
            return executeQuery(sql);
        }
        public Boolean suaKhachHangNCC()
        {
            string sql = "update KhachHangNCC set manhomkhncc = '" + manhomkhncc + "',tenkhncc=N'" + tenkhncc + "',diachikhncc=N'" + diachikhncc + "',sodtkhncc='" + sodtkhncc + "',emailkhncc='" + emailkhncc + "',faxkhncc='" + faxkhncc + "',sotaikhoankhncc='" + sotaikhoankhncc + "',masothuekhncc='" + masothuekhncc + "',ghichukhncc=N'" + ghichukhncc + "',congnobandaukhncc = " + congnobandaukhncc + " where makhncc = '" + makhncc + "'";
            return executeQuery(sql);
        }
        public Boolean xoaKhachHangNCC(string makhncc)
        {
            string sql = "delete KhachHangNCC where makhncc = '" + makhncc + "'";
            return executeQuery(sql);
        }
       
        public Boolean isExistKhachHangNCC(string makhncc)
        {
            string sql = "select makhncc from KhachHangNCC where makhncc = '" + makhncc + "'";
            return isExist(sql);
        }
        public DataTable layDSTatCaKhachHangNCC()
        {
           
            //string sql = "select a.manhomkhncc,makhncc,tenkhncc, diachikhncc,sodtkhncc, emailkhncc, faxkhncc,sotaikhoankhncc,masothuekhncc,ghichukhncc ";
            string sql = "select a.manhomkhncc,makhncc,tenkhncc, diachikhncc ";
            sql += "from  NhomKhachHangNCC a,KhachHangNCC b where a.manhomkhncc = b.manhomkhncc and makhncc <>'MD'";

            return getDataTable(sql);
        }
        public DataTable layDSKhachHangNCCTheoLoai(int loaikhncc)
        {
            
            string sql = "select a.manhomkhncc,makhncc,tenkhncc, diachikhncc,sodtkhncc, emailkhncc, faxkhncc,sotaikhoankhncc,masothuekhncc,ghichukhncc,congnobandaukhncc ";
            sql += "from  NhomKhachHangNCC a,KhachHangNCC b where a.manhomkhncc = b.manhomkhncc and loaikhncc = " + loaikhncc + " and makhncc <> 'MD' order by manhomkhncc";
            
           
            return getDataTable(sql);
        }
        public string taoMaKhachHangNCC()
        {
            string sql = "select max(convert(int,manhomkhncc)) from NhomKhachHangNCC";
            return string.Format("{0,6:000000}", getNumberValue(sql) + 1);
        }

        public DataTable layThongTinKhachHangNCC(string makhncc)
        {
            string sql = "select * from KhachHangNCC where makhncc = '" + makhncc + "'";
            return getDataTable(sql);
        }
       
        
        public DataTable layDSPhieuNhapXuatCuaKHNCC(string makhncc)
        {
            string sql = "select * from PhieuNhapXuat where makhncc = '" + makhncc + "'";
            return getDataTable(sql);
        }
        public DataTable layDSPhieuThuChiCuaKHNCC(string makhncc)
        {
            string sql = "select * from PhieuThuChi where makhncc = '" + makhncc + "'";
            return getDataTable(sql);

        }
       
        public double layCongNoHienTaiKHNCC(string makhncc,string sort)
        {
            //string sql = "select congno from v_CongNoHienTaiKHNCC where makhncc = '" + makhncc + "'";
            //return getNumberValue(sql);
            //string sql = "";
            //double number = 0;
            //if (getNumberValue("select * from "))
            string sql = "select distinct conno ";
            sql += "from v_TheoDoiCongNoKHNCC a ";
            sql += "where makhncc = '" + makhncc + "' and sort = (select max(sort) from v_TheoDoiCongNoKHNCC where sort <= '" + sort + "' and makhncc = '" + makhncc + "')";
            return getNumberValue(sql);
        }
        
        public Boolean capNhatCongNoHienTaiKHNCC(string makhncc, double congnohientai)
        {
            string sql = "Update KhachHangNCC set congnobandaukhncc = " + congnohientai + " where makhncc = '" + makhncc + "'";
            return executeQuery(sql);
        }
        public DataTable layDSDoiTuongLienHe(string makhncc)
        {
            string sql = "select * from DoiTuongLienHe where makhncc = '" + makhncc + "'";
            return getDataTable(sql);
        }
        //public Boolean capNhatCongNoBanDauKHNCC(string makhncc, double congnobandau)
        //{
        //    string sql = "Update KhachHangNCC set congnobandaukhncc = " + congnobandau + " where makhncc = '" + makhncc + "'";
        //    return executeQueryTrans(sql);
        //}



        # region Report
        public DataTable inDSKhachHangNCC(string thongtindoanhnghiep, string tieudereport, int loaikhncc)
        {
            string sql = "select isnull(N'" + thongtindoanhnghiep + "','') as thongtindoanhnghiep,isnull(N'" + tieudereport + "','') as tieudereport,a.manhomkhncc,makhncc,tenkhncc, diachikhncc,sodtkhncc, emailkhncc, faxkhncc,sotaikhoankhncc,masothuekhncc,ghichukhncc ";
            sql += "from  NhomKhachHangNCC a,KhachHangNCC b where a.manhomkhncc = b.manhomkhncc and loaikhncc = " + loaikhncc + " order by makhncc";
            return getDataTable(sql);
        }
        public DataTable theoDoiCongNoKHNCC(string thongtindoanhnghiep, string tieudereport, string tungay, string denngay, int loaikhncc)
        {
            string sql = "select isnull(N'" + thongtindoanhnghiep + "','') as thongtindoanhnghiep,isnull(N'" + tieudereport + "','') as tieudereport,isnull(N'" + tungay + "','') as tungay,isnull(N'" + denngay + "','') as denngay,tennhomkhncc,kh.makhncc, tenkhncc, diachikhncc,sodtkhncc ,isnull(nodauki,0) as nodauki,isnull(tienmuatk,0) as tienmuatk,isnull(tientratk,0) as tientratk,(isnull(nodauki,0) + isnull(tienmuatk,0) - isnull(tientratk,0)) as nocuoiki ";
            sql += "from (select tennhomkhncc,makhncc, tenkhncc, diachikhncc,sodtkhncc from KhachHangNCC a, NhomKhachHangNCC b where loaikhncc = " + loaikhncc + " and a.manhomkhncc = b.manhomkhncc) kh ";
            sql += "left join   ";

            sql += "(select a.makhncc,( isnull(tienmuadk,0) - isnull(tientradk,0)) as nodauki   ";
            sql += "from KhachHangNCC a   ";
            sql += "left join   ";
            sql += "(select makhncc,isnull(sum(tongtienso),0) as tienmuadk from PhieuNhapXuat    ";
            sql += "where  convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by makhncc) tienmuahangdk  ";
            sql += "on a.makhncc = tienmuahangdk.makhncc left join   ";
            sql += "(select makhncc,isnull(sum(sotienso),0) as tientradk  from PhieuThuChi   ";
            sql += "where  convert(datetime,ngaythuchi,103) <convert(datetime,'" + tungay + "',103) group by makhncc) tientradk  ";
            sql += "on a.makhncc = tientradk.makhncc) nodauki  ";
            sql += "on kh.makhncc = nodauki.makhncc left join ";
            sql += "(select makhncc,isnull(sum(tongtienso),0) as tienmuatk  from PhieuNhapXuat    ";
            sql += "where  convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)     ";
            sql += "group by makhncc) muatrongki  ";
            sql += "on kh.makhncc = muatrongki.makhncc left join  ";
            sql += "(select makhncc,isnull(sum(sotienso),0) as tientratk  from PhieuThuChi   ";
            sql += "where  convert(datetime,ngaythuchi,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaythuchi,103) <= convert(datetime,'" + denngay + "',103)  ";
            sql += "group by makhncc) tratrongki on kh.makhncc = tratrongki.makhncc where  (isnull(nodauki,0) + isnull(tienmuatk,0) - isnull(tientratk,0)) > 0";
            return getDataTable(sql);
        }
        public DataTable layTatCaCongNoKhachHang(string thongtindoanhnghiep, string tieudereport, string tungay, string denngay)
        {
            string[] strPara = { "@thongtindoanhnghiep", "@tieudereport", "@tungay", "@denngay" };
            string[] strValue = { thongtindoanhnghiep, tieudereport, tungay, denngay };
            return getDataTableSP("sp_CongNoKhachHang", strPara, strValue);
        }
        public DataTable layCongNoChiTietKhachHang(string thongtindoanhnghiep,  string tungay, string denngay, string makhncc)
        {
            string[] strPara = { "@thongtindoanhnghiep",  "@tungay", "@denngay", "@makhncc" };
            string[] strValue = { thongtindoanhnghiep,  tungay, denngay, makhncc };
            return getDataTableSP("sp_CongNoChiTietKhachHang", strPara, strValue);
        }
        public DataTable layTatCaCongNoNhaCungCap(string thongtindoanhnghiep, string tieudereport, string tungay, string denngay)
        {
            string[] strPara = { "@thongtindoanhnghiep", "@tieudereport", "@tungay", "@denngay" };
            string[] strValue = { thongtindoanhnghiep, tieudereport, tungay, denngay };
            return getDataTableSP("sp_CongNoNhaCungCap", strPara, strValue);
        }
        public DataTable layCongNoChiTietNhaCungCap(string thongtindoanhnghiep,  string tungay, string denngay, string makhncc)
        {
            string[] strPara = { "@thongtindoanhnghiep",  "@tungay", "@denngay", "@makhncc" };
            string[] strValue = { thongtindoanhnghiep, tungay, denngay, makhncc };
            return getDataTableSP("sp_CongNoChiTietNhaCungCap", strPara, strValue);
        }
        #endregion
       
        
        
        
       

    }
}
